The following procedure shows how to define a new, simple query. To edit an existing query, you can follow the same procedure. In some cases, a system administrator can navigate to the webpage where the results are displayed and click Edit Source Query. See Editing query-generated lists in iMIS for more information.
To create or edit a query in Basic mode
If you have been granted access to an IQA folder, you can edit or create queries in that folder.
1. From Tools > Intelligent Query Architect, select Edit> Query or New> Query.
2. Make sure Basic mode is selected.
3. Enter a Name and (optional) a Description for the query. Do not use apostrophes (‘).
Tip: Use a naming convention that ensures that the query has a unique name. iMIS does not require uniqueness.
4. Click the Sources tab and click Add Source. Navigate to find a business object or query to use as a source for the query. (Most business objects are located in the Common folder.) Select the desired source and click OK.
Note: When you add a source more than once, the system automatically appends a numeric value (beginning with the number 1) to the second and subsequent sources to distinguish between the sources. For example, if you add the Contact business object three times in order to set up a variety of relationships, the instances would be labeled Contact, Contact1, and Contact2.
5. (optional) Select an additional source and click OK. Use the up and down arrows to reorder the data sources as needed.
If you select more than one source, you must define the relationships among the sources. Most sources have a common relationship as a default. Use the default, choose the desired relation from the list, or select Custom from the drop-down list to define a new one.
6. (optional) Click the Filters tab and define the filtering rules:
□ Select the Property to be compared.
□ Select the type of Comparison, such as Equal, Between, or Empty. Enter the Value or select a date for comparison.
□ Click the icon to add the filter.
■ When you select Between, the Value column changes to display two fields with the word "and" between them. The specified values are inclusive.
■ The pattern comparisons (Contains, Not Contains, Starts With, and Ends With) use a simple text field even for a Property that normally contains a discrete drop-down list of values.
■ The Contains comparison type performs a string comparison to find one string within another string. A partial match (for example, only a portion of a word) is required.
■ Enter multiple values for a pattern comparison (Contains, Not Contains, Starts With, and Ends With) by separating each value with a comma.
■ Click the calendar icon next to any Date field to select a date. If you need to specify a specific time on the selected date, first enter the time in the Date field, using 24-hour format (for example, enter 23:00 for 11:00 p.m.) then click the calendar icon to select the date.
■ When entering numbers for a comparison on a date field (Within Days, Within Months, Within Years), be aware that such comparisons are zero-based. For example, if you select the Last Updated column and select Within Days, you must enter zero in the Value field to get all records that were updated within one day or enter 1 to get records that were updated within two days.
7. Click the Display tab and define how results columns will be displayed:
□ Select the columns you want to work with (Selected, Defaults, or All) from the View list.
□ Select the Only display unique results option to prevent duplicate results from displaying. When the query includes a property with a display function such as Count, that property is used to determine unique results. If the query includes more than one display function, only the first function property displayed (defined by the display order) is used.
□ Select the results columns to Display to users. Select the Order in which you want the columns to display to users (from left to right). The order is based on the Order column, not the order in which they were chosen. Subsequent sorting selections go at the end of the sort order list (per session) by order listed within each session.
Tip: If you are defining a query for a merge output process, make sure you select all columns needed for the merge.
8. (optional) Click the Sorting tab and specify the sort order for each column:
□ Select the Property to sort by.
□ Select the sort Priority for each Property.
□ Select the Direction of data in each column: Ascending or Descending.
□ Click the + icon to add the sort.
The user can sort columns in the output by clicking on a column header. A carat symbol (^ ) will display in the column header, indicating that the column is now a sort column. The user-selected sort overrides any sorting defined on the Sorting page and will change the sort definition in the query to reflect the current column sort.
9. Select Run to test the query. If necessary, select Define to edit the query.
10. Select Security and select the Access Mode option to assign access and privileges for the query and its results display. The default setting for a new query is “Authenticated Users Full Control”. Names of authorized users are listed in the Current Access List.
Access Key and Contact Key are important when you are linking to objects with security, such as a Campaign business object where a user must have certain security to be able to view data.
11. Click Save. Select the folder in which to save the query.
12. (optional) Click Report to save this query as a report definition. You can customize these reports and export them to PDF or XLS format. See Reporting in IQA for more information.